package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.CurrencyDao;
import csc.fresher.entity.Currency;
import csc.fresher.utils.DBUtils;
import csc.fresher.utils.DatabaseUtil;

@Repository
public class CurrencyDaoImpl implements CurrencyDao{
	@Autowired
	DataSource dataSource;
	
	private static final Logger logger = Logger.getLogger(CurrencyDaoImpl.class);
	
	@Override
	public void insert(Currency currency) {
		// TODO Auto-generated method stub
		String query = "INSERT INTO `Currency` (`CurrencyID`,`Currency`) VALUES (?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, currency.getCurrencyID());
			preparedStatement.setString(2, currency.getCurrency());
			preparedStatement.execute();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public void update(Currency currency) {
		// TODO Auto-generated method stub
		String query = "UPDATE `Currency` "
				+ " SET  `Currency`.`Currency`=?"
				+ "WHERE `Currency`.`CurrencyID`=?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, currency.getCurrencyID());
			preparedStatement.setString(2, currency.getCurrency());
			preparedStatement.execute();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public Currency getCurrencyByID(int currencyID) {
		// TODO Auto-generated method stub
		String query = "SELECT * FROM `Currency` WHERE `Currency`.`CurrencyID`=?";
		Currency currency = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, currencyID);
			rs = preparedStatement.executeQuery();
			if(rs.next())
				currency = getCurrencyResultSet(rs);
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs, logger);
		}
		return currency;
	}

	private Currency getCurrencyResultSet(ResultSet rs) throws SQLException{
		Currency currency = new Currency();
		currency.setCurrencyID(rs.getInt("CurrencyID"));
		currency.setCurrency(rs.getString("Currency").toUpperCase());
		return currency;
	}
	
	@Override
	public List<Currency> getAllCurrency() {
		// TODO Auto-generated method stub
		List<Currency> Currency = null;
		Currency currency = null;
		String query = "SELECT * FROM `Currency`";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			rs = DatabaseUtil.executeQuery(preparedStatement, connection,
					query, null);
			Currency = new ArrayList<>();
			while(rs.next()){
				currency = new Currency();
				currency.setCurrencyID(rs.getInt("CurrencyID"));
				currency.setCurrency(rs.getString("Currency"));
				Currency.add(currency);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DatabaseUtil.closeResultSetConnection(connection,
					preparedStatement, rs, logger);
		}
		return Currency;
	}

}
